<?xml version="1.0" encoding="utf-8"?>

<dbTestFixture
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:noNamespaceSchemaLocation="file:/C:/TestFileSchema2.xsd">
  <usage>
    <![CDATA[ 
    Function introduction:
       1. Performance test for stored procedure and sql.
       2. Unit test for stored procedure and sql. 
  
    Remark:
      1. Argument size:
         For argument is variable-length data type, such as char or varchar, if its direction is Output or Return or AnonymousOutput, size attribute is mandatory. 
         if their direction is InputOutput, size attribute is also recommended to assign.
      2. exportRecordCount must be in -1, 0, other integer value greater than 0. 
             If =-1, it will output all records 
             if =0, it will not output any record
             if =Value, it will output the given number of records 
      3. For Query SQL, the name of return argument can be any string.
      4. In SQL programming perspective, there are two kinds of database whether the database supports anonymous-output argument or not.
         e.g. Oracle does not allow anonymous-output in stored procedure, because all resultsets of a stored procedure must be carried by those explicited arguments. 
         MS SQL Server and MySQL are anonymous-output supported database.
         Caution: 
           For database supporting anonymous-output, the order of each argument declaration is very important because the resultsets are retrieved according to the position of argument declaration. 
		     e.g. In MS SQL Server, if there are anonymous-output resultsets, the anonymous-output arguments must locate prior to real output arguments. 
           For database not supporting anonymous-output, the order of each argument declaration is not relevant at all. Because the resultsets are retrieved according to their argument names.
    ]]>
  </usage>

  <globalSetting exportRecordCount="200" wrapStringByCDATA="false" dbConnectionFile="dbCfg.xml">
  <dbConnection connectionName="db1" 
        connectionString="User Id=hr;Password=hr;Server=vm_10;Connect Mode=Default;Unicode=True"
        providerName="Devart.Data.Oracle" />		 
  </globalSetting>

  <procDeclarations>
    <procDeclaration alias="SP_GET_ADDRESS_SORTBY_LOC"  name="SP_GET_ADDRESS_SORTBY_LOC" dbConnection="db1" namespace="HR.HR_UNITTEST"  >
      <arguments>
        <argument name="REGION_CODE" direction="Input"  type="ORA_INT"  />
        <argument name="RETURN_TABLE" direction="Output"  type="ORA_CURSOR"   />
      </arguments>
      <remark>Get address order by location id</remark>
    </procDeclaration>

    <procDeclaration alias="SP_GET_ADDRESS_SORTBY_ZIP"  name="SP_GET_ADDRESS_SORTBY_ZIP" dbConnection="db1" namespace="HR.HR_UNITTEST"  >
      <arguments>
        <argument name="REGION_CODE" direction="Input"  type="ORA_INT"  />
        <argument name="RETURN_TABLE" direction="Output"  type="ORA_CURSOR"   />
      </arguments>
      <remark>Get address order by postcode</remark>
    </procDeclaration>

    <procDeclaration alias="SP_GET_ITALY_ADDRESS"  name="SP_GET_ITALY_ADDRESS" dbConnection="db1" namespace="HR.HR_UNITTEST"  >
      <arguments>
        <argument name="RETURN_TABLE" direction="Output"  type="ORA_CURSOR"    />
      </arguments>
      <remark>get all addess in italy</remark>
    </procDeclaration>


  </procDeclarations>


  <sqlDeclarations>
    
    <sqlDeclaration alias="Sql_GetCountFromExcel1" dbConnection="Excel_Scalar">
      <sql>
        <![CDATA[  
        Select count from [Sheet1$A1:A2]
        ]]>
      </sql>
      <arguments>
        <argument name="RETURN_Value" direction="Return"  type="XLS_INT"  />
      </arguments>
      <remark> Get one integer value from excel</remark>
    </sqlDeclaration>

    <sqlDeclaration alias="Sql_GetAddressesFromExcel2" dbConnection="Excel_Refer">
      <sql>
        <![CDATA[ 
        Select CITY,STREET from [Sheet1$]
        ]]>
      </sql>
      <arguments>
        <argument name="RETURN_Table" direction="Return"  type="XLS_RECORDSET"  />
      </arguments>
      <remark> Get one recordset from excel</remark>
    </sqlDeclaration>

    <sqlDeclaration alias="Sql_Call_FUN_GET_UPPER_STR" dbConnection="db1">
      <sql>
        <![CDATA[ select HR.HR_UNITTEST.FUN_GET_UPPER_STR(:name) from dual ]]>
      </sql>
      <remark>call FUN_GET_UPPER_STR function</remark>
      <arguments>
        <argument name="Name" direction="Input"  type="ORA_VARCHAR2" size="100"   />
        <argument name="RETURN_VALUE22" direction="Return"  type="ORA_VARCHAR2" size="100"  />
      </arguments>
    </sqlDeclaration>
 
    <sqlDeclaration alias="SQL_GET_CONST" dbConnection="db1">
      <sql>
        <![CDATA[ select  :x from dual; ]]>
      </sql>
      <arguments>
        <argument name="x" direction="Input"  type="ORA_INT"  />
        <argument name="ResultValue" direction="Return"  type="ORA_INT"  />
      </arguments>
      <remark> Get constant integer value from oracle db</remark>
    </sqlDeclaration>

    <sqlDeclaration alias="SQL_GET_CONST_STRING" dbConnection="db1">
      <sql>
        <![CDATA[ select  :x from dual; ]]>
      </sql>
      <arguments>
        <argument name="x" direction="Input"  size="100" type="ORA_VARCHAR2"  />
        <argument name="ResultValue" direction="Return"  type="ORA_VARCHAR2"  />
      </arguments>
      <remark> Get constant string value from oracle db</remark>
    </sqlDeclaration>
    
  </sqlDeclarations>

  <perfTestCases>
    <perfTestCase testCaseName="PT_SP_GET_ADDRESS_SORTBY_LOC" statementAlias="SP_GET_ADDRESS_SORTBY_LOC" ignoreThis="false" testInSandbox="false">
      <testCaseRemark>test for sp SP_GET_ADDRESS_SORTBY_LOC</testCaseRemark>
      <inputArguments>
        <argument name="REGION_CODE" value="2"  />
      </inputArguments>
    </perfTestCase>

    <perfTestCase testCaseName="PT_FUN_GET_UPPER_STR" statementAlias="SQL_CALL_FUN_GET_UPPER_STR" ignoreThis="false" testInSandbox="true">
      <testCaseRemark>test for function PT_FUN_GET_UPPER_STR</testCaseRemark>
      <inputArguments>
        <argument name="Name"  value="abcdef"  />
      </inputArguments>
    </perfTestCase>

    <perfTestCase testCaseName="PT_SQL_GET_CONST" statementAlias="SQL_GET_CONST" ignoreThis="false" testInSandbox="true">
      <testCaseRemark>test sql statement of SQL_GET_CONST</testCaseRemark>
      <inputArguments>
        <argument name="x"  value="10"  />
      </inputArguments>
    </perfTestCase>
  </perfTestCases>


  <unitTestCases>

    <unitTestCase testCaseName="UT_SP_GET_ADDRESS"  assertion="SetEqual"  ignoreThis="false" testInSandbox="true">
      <testCaseRemark>unit test SP_GET_ADDRESS_SORTBY_LOC and SP_GET_ADDRESS_SORTBY_ZIP, assert they are SetEqual </testCaseRemark>

      <setup>
        <!--all statments in setup section will be invoked at the beginning of unit testing-->
        <statement statementAlias="SQL_GET_CONST" >
          <inputArguments>
            <argument name="x"  value="-1"  />
          </inputArguments>
        </statement>
        <statement statementAlias="SQL_GET_CONST" >
          <inputArguments>
            <argument name="x"  value="-2"  />
          </inputArguments>
        </statement>
      </setup>

      <teardown>
        <!--all statments in teardown section will be invoked at the end of unit testing-->
        <statement statementAlias="SQL_GET_CONST" >
          <inputArguments>
            <argument name="x"  value="1"  />
          </inputArguments>
        </statement>
        <statement statementAlias="SQL_GET_CONST" >
          <inputArguments>
            <argument name="x"  value="2"  />
          </inputArguments>
        </statement>
      </teardown>

      <targetResultset statementAlias="SP_GET_ADDRESS_SORTBY_LOC"  outputArgument="RETURN_TABLE">
        <inputArguments>
          <argument name="REGION_CODE" value="1"  />
        </inputArguments>
        <outputSetting>
          <field name="CITY" outputName="CITY"/>
          <field name="STREET_ADDRESS" outputName ="STREET"/>
        </outputSetting>
      </targetResultset>

      <referenceResultset statementAlias="SP_GET_ADDRESS_SORTBY_ZIP"  outputArgument="RETURN_TABLE">
        <inputArguments>
          <argument name="REGION_CODE" value="1"  />
        </inputArguments>
        <outputSetting>
          <field name="CITY" outputName="CITY"/>
          <field name="STREET_ADDRESS" outputName ="STREET"/>
        </outputSetting>
      </referenceResultset> 
    </unitTestCase>

    <unitTestCase testCaseName="UT_SP_GET_ADDRESS_LOC_DATA"  assertion="SetEqual"  ignoreThis="false" testInSandbox="true">
      <testCaseRemark>unit test SP_GET_ADDRESS_SORTBY_LOC, to verfiy the resultset data, assert they are SetEqual </testCaseRemark>

      <targetResultset statementAlias="SP_GET_ADDRESS_SORTBY_LOC"  outputArgument="RETURN_TABLE">
        <inputArguments>
          <argument name="REGION_CODE" value="1"  />
        </inputArguments>
        <outputSetting>
          <field name="CITY" outputName="CITY"/>
          <field name="STREET_ADDRESS" outputName ="STREET"/>
        </outputSetting>
      </targetResultset>

      <referenceResultset statementAlias="Sql_GetAddressesFromExcel2"  outputArgument="RETURN_TABLE">
        <outputSetting>
          <field name="CITY" outputName="CITY"/>
          <field name="STREET" outputName ="STREET"/>
        </outputSetting>
      </referenceResultset>

    </unitTestCase>

    <unitTestCase testCaseName="UT_GET_ITALY_ADDRESS"  assertion="IsSupersetOf"  ignoreThis="false" testInSandbox="true">
      <testCaseRemark>unit test SP_GET_ADDRESS_SORTBY_LOC and SP_GET_ITALY_ADDRESS, assert the former includes the latter</testCaseRemark>
     
      <targetResultset statementAlias="SP_GET_ADDRESS_SORTBY_LOC"  outputArgument="RETURN_TABLE">
        <inputArguments>
          <argument name="REGION_CODE" value="1"  />
        </inputArguments>
      </targetResultset>

      <referenceResultset statementAlias="SP_GET_ITALY_ADDRESS"  outputArgument="RETURN_TABLE">
      </referenceResultset>
    </unitTestCase>

    <unitTestCase testCaseName="UT_ITALY_CITYS_COUNT"  assertion="RecordCountIsEqual"  ignoreThis="false" testInSandbox="true">
      <testCaseRemark>unit test SP_GET_ITALY_ADDRESS, assert its resultset count is 2</testCaseRemark>
      <targetResultset statementAlias="SP_GET_ITALY_ADDRESS"  outputArgument="RETURN_TABLE">
      </targetResultset>

      <referenceResultset statementAlias="SQL_GET_CONST"  outputArgument="ResultValue">
        <inputArguments>
          <argument name="x"  value="2"  />
        </inputArguments>
      </referenceResultset>
    </unitTestCase>

    <unitTestCase testCaseName="UT_ITALY_CITYS_COUNT_EXCEL"  assertion="RecordCountIsEqual"  ignoreThis="false" testInSandbox="true">
      <testCaseRemark>unit test SP_GET_ITALY_ADDRESS, assert its resultset count is 2</testCaseRemark>
      <targetResultset statementAlias="SP_GET_ITALY_ADDRESS"  outputArgument="RETURN_TABLE">
      </targetResultset>

      <referenceResultset statementAlias="Sql_GetCountFromExcel1"  outputArgument="RETURN_Value">
      </referenceResultset>
    </unitTestCase>

    <unitTestCase testCaseName="UT_FUN_GET_UPPER_STR" assertion="StrictEqual">
      <targetResultset statementAlias="Sql_Call_FUN_GET_UPPER_STR" outputArgument="RETURN_VALUE22">
        <inputArguments>
          <argument name="name" value="abcdef"/>
        </inputArguments>
      </targetResultset>
      <referenceResultset statementAlias="SQL_GET_CONST_string" outputArgument="ResultValue">
        <inputArguments>
          <argument name="x" value="ABCDEF"/>
        </inputArguments>
      </referenceResultset>
    </unitTestCase>
  </unitTestCases>


</dbTestFixture>




